﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Classes;

namespace FlightSystemDBLayer
{
    public class DBFlight : IFlight
    {
        IList<City> realCities;

        //private static SqlCommand cmd = null;

        public DBFlight(IList<City> realCities)
        {
             this.realCities = realCities;
        }
        public IList<Flight> GetFlights()
        {
            DataSet ds = new DataSet();

            IList<Flight> list = new List<Flight>();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"select * FROM Flight", SQLAccess.dbconn);
            da.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                list.Add(CreateFlight(Convert.ToInt32(dr["IDDepartureCity"]), Convert.ToInt32(dr["IDArrivalCity"]), dr["Company"].ToString(), Convert.ToInt32(dr["NumberOfSeats"])));
            }
            return list;

        }


        public Flight CreateFlight(int cityFromID, int cityToID, string company, int noOfSeats)
        {

            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();
            da.SelectCommand = new SqlCommand(@"select * FROM City WHERE IDCity = " + cityFromID + "", SQLAccess.dbconn);
            da.Fill(ds1);
            da.SelectCommand = new SqlCommand(@"select * FROM City WHERE IDCity = " + cityToID + "", SQLAccess.dbconn);
            da.Fill(ds2);


            //Console.WriteLine(ds1.Tables[0].Rows[0][2]);
            City from = null;
            foreach(City c in realCities)
            {
                if (c.Name == ds1.Tables[0].Rows[0][1].ToString())
                {
                    from = c;
                }
  
            }
            City to = null;
            foreach(City f in realCities)
            {
                if (f.Name == ds2.Tables[0].Rows[0][1].ToString())
                {
                    to = f;
                }
            }


            return new Flight(from, to);
            //string sql = @"insert into Flight values (" + cityFromID + ", " + cityToID + ", " + company + ", " + noOfSeats + ",)";
            //SqlCommand cmd = SQLAccess.GetDbCommand(sql);
            //cmd.ExecuteNonQuery();
            //SQLAccess.Close();
        }


        public Flight FindFlightByID(int idFlight)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(@"select * FROM Flight WHERE IDFlight = " + idFlight + "", SQLAccess.dbconn);
            da.Fill(ds);
            DataRow dr = ds.Tables[0].Rows[0];
            Flight flight = CreateFlight(Convert.ToInt32(dr["IDDepartureCity"]), Convert.ToInt32(dr["IDArrivalCity"]), dr["Company"].ToString(), Convert.ToInt32(dr["NumberOfSeats"]));
            return flight;
     
        }

    }
}
